Intelligent BlackMan
   |
Posted - 2006.12.06 03:56:00 -
[1]
Edited by: Intelligent BlackMan on 06/12/2006 03:59:10
Edited by: Intelligent BlackMan on 06/12/2006 03:58:48
Here are some SQL queries that might be helpful to some people.
This one is a simple one that gives you some basic BPO information:
use EVE;
selectit.typeName, productionTime, techLevel, researchProductivityTime,
researchMaterialTime, researchCopyTime, researchTechTime, productivityModifier,
materialModifier, wasteFactor, maxProductionLimit
frominvTypes as it join invBlueprintTypes as ibt
on it.typeID = ibt.blueprintTypeID
join invTypes as it_prod
on ibt.productTypeID = it_prod.typeID
order by it.typeName
Here's a rather complex one that returns data for BPOs that only use minerals (no tech2, no capital ships, etc) and does it such that the mineral ammounts end up in their own columns:
use EVE;
select * from (
selectit.typeName "Blueprint Name", it_prod.typeName Produces,
max(it.basePrice) basePrice,
max(ibt.productionTime) productionTime,
max(ibt.techLevel) techLevel,
max(ibt.researchProductivityTime) productivityTime,
max(ibt.researchMaterialTime) materialTime,
max(ibt.researchCopyTime) copyTime,
SUM(case when it_mat.typeName = 'Tritanium' then t2.quantity else 0 end) as 'Tritanium',
SUM(case when it_mat.typeName = 'Pyerite' then t2.quantity else 0 end) as 'Pyerite',
SUM(case when it_mat.typeName = 'Mexallon' then t2.quantity else 0 end) as 'Mexallon',
SUM(case when it_mat.typeName = 'Isogen' then t2.quantity else 0 end) as 'Isogen',
SUM(case when it_mat.typeName = 'Nocxium' then t2.quantity else 0 end) as 'Nocxium',
SUM(case when it_mat.typeName = 'Zydrine' then t2.quantity else 0 end) as 'Zydrine',
SUM(case when it_mat.typeName = 'Megacyte' then t2.quantity else 0 end) as 'Megacyte',
SUM(case when t2.requiredTypeID < 34 or t2.requiredTypeID > 40 then 1 else 0 end) as 'numNonBasicMaterials'
frominvTypes as it join invBlueprintTypes as ibt
on it.typeID = ibt.blueprintTypeID
join invTypes as it_prod
on ibt.productTypeID = it_prod.typeID,
TL2MaterialsForTypeWithActivity as t2 join invTypes as it_mat
on t2.requiredTypeID = it_mat.typeID
whereit.marketGroupID is not null
and t2.typeID = it.typeID
and t2.quantity > 0
group by it.typeName, it_prod.typeName ) as data
where numNonBasicMaterials = 0
These work for me in MS SQL Server Express but should be pretty standard. Change that "use EVE" to use your database name if you didn't import your data into a database named "EVE".
|